﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;
namespace BookStore.Controllers
{
    public class StaffController : Controller
    {
        private static StaffController _controller = null;
        public StaffController()
        {
            _controller = this;
        }
        public static StaffController getController()
        {
            if (_controller == null)
                return new StaffController();
            else
                return _controller;
        }


        public bool checkLogin(Staff login)
        {
            bool kq = false;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmdCheckLogin = new SqlCommand("BS_CheckLogin", con);
                cmdCheckLogin.CommandType = CommandType.StoredProcedure;
                cmdCheckLogin.Parameters.Add("@acc", SqlDbType.NVarChar).Value = login.Acount;
                cmdCheckLogin.Parameters.Add("@pass", SqlDbType.NVarChar).Value = login.Password;                
                var result = cmdCheckLogin.ExecuteReader();
                if (result.HasRows)
                {
                    result.Read();
                    login.StaffID = (int)result["StaffID"];
                    login.PhoneNumber = (string)result["PhoneNumber"];
                    login.Address = (string)result["Address"];
                    login.StaffName = (string)result["StaffName"];                    
                    login.Fk_PositionID = (string)result["PositionID"];
                    login.Working = (int)result["Working"];
                    kq = true;
                }
            }
            catch (Exception e)
            {
               // throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return kq;
        }

        public void AddStaffToDatabase(String Account, String Password, String PositionID, String StaffName, String Address, String PhoneNumber)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddStaffToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Account", SqlDbType.NVarChar).Value = Account;
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = Password;
                cmd.Parameters.Add("@PositionID", SqlDbType.NVarChar).Value = PositionID;
                cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = StaffName;
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
                cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = PhoneNumber;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
                }
                finally
            {
                 con.Dispose();
                con.Close();
            }
        }

        public void DismissStaffFromDatabase(String StaffID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DismissStaffFromDatabase",con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = StaffID;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ReinstateStaffToDatabase(String StaffID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ReinstateStaffToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = StaffID;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyStaffFromDatabase(String StaffID, String Account, String Password, String PositionID, String StaffName, String Address, String PhoneNumber)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyStaffFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = StaffID;
                cmd.Parameters.Add("@Account", SqlDbType.NVarChar).Value = Account;
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = Password;
                cmd.Parameters.Add("@PositionID", SqlDbType.NVarChar).Value = PositionID;
                cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = StaffName;
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
                cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = PhoneNumber;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this staff because it's attribute!", "Error : Modify Staff", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayStaff(String Staff, int check)
        {
            DataTable dt = new DataTable("DisplayStaff");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Staff From StaffID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayStaffFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Staff;
                }
                // Display Staff From StaffName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_DisplayStaffFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = Staff;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllStaff()
        {
            DataTable dt = new DataTable("DisplayAllStaff");
            String sql = @"SELECT [StaffID],[Account],[Password],[PositionID],[StaffName],[Address],[PhoneNumber],[Working]
                           FROM [BookStore].[dbo].[STAFF]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable SearchStaffInformation(String Account, String Password, String PositionID, String StaffName, String Address, String PhoneNumber)
        {
            DataTable dt = new DataTable("SearchStaffInformation");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_SearchStaffInformation", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Account", SqlDbType.NVarChar).Value = Account + "%";
                cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = Password + "%";
                cmd.Parameters.Add("@PositionID", SqlDbType.NVarChar).Value = PositionID + "%";
                cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = StaffName + "%";
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address + "%";
                cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = PhoneNumber + "%";
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean checkAccountOfStaff(String Account)
        {
            DataTable dt = new DataTable("checkAccountOfStaff");
            String sql = @"SELECT *
                            FROM [BookStore].[dbo].[STAFF]
                            WHERE [Account] ='" + Account + "'";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public String getAccountOfStaff(String StaffID)
        {
            DataTable dt = new DataTable("getAccountOfStaff");
            String sql = @"SELECT [Account]
                            FROM [BookStore].[dbo].[STAFF]
                            WHERE [StaffID] ='" + StaffID + "'";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if (dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return "";
        }

        public Boolean CheckStaffInDatabase(String Staff, int check)
        {
            DataTable dt = new DataTable("CheckStaffInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Staff From StaffID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckStaffFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Staff;
                }
                // Check Staff From StaffName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_CheckStaffFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = Staff;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean CheckStaffName(String staffName)
        {
            for (int i = 0; i < staffName.Length; i++)
            {
                if ((staffName[i].ToString() == "\\") || (staffName[i].ToString() == ";") || (staffName[i].ToString() == ")") || (staffName[i].ToString() == "@") ||
                    (staffName[i].ToString() == "#") || (staffName[i].ToString() == "$") || ((staffName[i].ToString() == "%") || (staffName[i].ToString() == "&")))
                    return false;
            }
            return true;
        }

        public Boolean CheckWorkOfStaff(String staff)
        {
            DataTable dt = new DataTable("CheckWorkOfStaff");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_CheckWorkOfStaff", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = staff;
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if ((dt.Rows.Count > 0) && (dt.Rows[0][0].ToString() == "1"))
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public String getNumericCodeStaff()
        {
            String StaffID;
            String temp;
            Decimal numeric;
            DataTable dt = new DataTable("getNumericCodeStaff");
            String sql = @"SELECT [StaffID]
                           FROM [BookStore].[dbo].[STAFF]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
                StaffID = dt.Rows[dt.Rows.Count - 1][0].ToString();
                numeric = Convert.ToDecimal(StaffID);
                numeric += 1;
                temp = numeric.ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return temp;
        }
    }
}
